﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using SoufunData.DB;

namespace SoufunData
{
    public partial class ExToDbBBasicInfo : DevExpress.XtraEditors.XtraForm
    {
        public ExToDbBBasicInfo()
        {
            InitializeComponent();
        }

        private void btn_excelPath_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {

            openFileDialog1.Filter = "Excel文件|*.xls;*.xlsx";
            openFileDialog1.FileName = "";
            openFileDialog1.ShowDialog();
            string fileName = openFileDialog1.FileName;
            btn_excelPath.Text = fileName;

            gridControl2.DataSource = null;
            gridView2.Columns.Clear();
            gridView2.GroupPanelText = "请选择Excel文件";
            if (fileName == "")
            {
                return;
            }
            DataSet ds = Common.LoadDataFromExcel(fileName);
            if (ds == null || ds.Tables.Count == 0)
            {
                string strError = "读取Excel时发生错误，请核对：\r\n ";
                strError += "1，导入时请在Excel中关闭想要导入的文件！\r\n";
                strError += "2，导入的Excel表中的工作表名称必须为Sheet1,否者程序无法读取！\r\n";
                strError += "3，请确保您的计算机安装了Microsoft Office！\r\n";
                strError += "4，请确保导入的Excel文件正常！";
                XtraMessageBox.Show(strError);
                btn_excelPath.Text = "";
                return;
            }
            List<string> deleteColumns = new List<string>();
            foreach (DataColumn column in ds.Tables[0].Columns)
            {
                if (column.ColumnName.Contains("F"))
                {
                    deleteColumns.Add(column.ColumnName);
                }
            }
            foreach (string column in deleteColumns)
            {
                ds.Tables[0].Columns.Remove(column);
            }
            if (!CheckExcelColumnsName(ds.Tables[0]))
            {
                btn_excelPath.Text = "";
                return;
            }
            gridControl2.DataSource = ds.Tables[0];
            gridView2.GroupPanelText = "共有" + ds.Tables[0].Rows.Count + "条数据";
        }


        private void AddData()
        {
           
            DataTable dtOld = (DataTable)gridControl2.DataSource;

            DataTable dt = dtOld.Copy();
            dt.Rows.Clear();
            foreach (DataRow dataRow in dtOld.Rows)
            { 
                dt.Rows.Add(dataRow.ItemArray);
            }

            SqlClass sqlClass = new SqlClass();
            List<string> existProject = new List<string>();
            foreach (DataRow row in dt.Rows)
            {
                string proName = row["项目名称"].ToString();
                if (sqlClass.ExistProjectName(proName, "tbbasicinfo"))
                {
                    existProject.Add(proName);
                }
            }
            if (existProject.Count>0)
            {
                StringBuilder strWarming = new StringBuilder();
                strWarming.Append("您选择的Excel表格中的以下项目在数据库中已存在,不能再次添加：\r\n");
                foreach (string sproName in existProject)
                {
                    strWarming.Append(sproName);
                    strWarming.Append("\r\n");
                }
                XtraMessageBox.Show(strWarming.ToString());
                BeginInvoke(new MethodInvoker(() =>
                {
                    btn_excelPath.Text = "";
                    gridControl2.DataSource = null;
                    btn_excelPath.Enabled = true;
                    btn_add.Enabled = true;
                }));
                return;
            }
            

            int insertRowsCount = 500;//一次性插入数据库行数
            int succeedrows = 0;//成功插入数据行数
            StringBuilder strSql = new StringBuilder();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow row = dt.Rows[i];
                string strProName = row["项目名称"].ToString();
                string strAddress = row["项目地址"].ToString();              
                string strOpenTime = row["开业时间"].ToString();
                strOpenTime = DateTime.Parse(strOpenTime).Date.ToString();
                string strBusinessType = row["商业业态类型"].ToString();
                string strSellState = row["销售状态"].ToString();
                string strXzq = row["所属行政区"].ToString();
                string strBigCompanyAroud = row["周边大型企业"].ToString();
                string strSupportingAroud = row["周边配套"].ToString().Trim();                 
                string strBusinessCircles = row["所属商圈"].ToString();
                string strCirclesLevel = row["商圈评级"].ToString();
                string strBusRouteCount = row["公交线路数量"].ToString();
                if (strBusRouteCount.Trim()=="" ||
                    strBusRouteCount == "-" || strBusRouteCount=="无")
                {
                    strBusRouteCount = "0";
                }
                string strBusRouteName = row["公交线路名称"].ToString();
                string strBusStationName = row["公交站点名称"].ToString();
                string strMetroRouteCount = row["地铁线路数量"].ToString();
                if (strMetroRouteCount.Trim() == "" ||
                    strMetroRouteCount == "-" || strMetroRouteCount == "无")
                {
                    strMetroRouteCount = "0";
                } 
                string strMetroRouteName = row["地铁线路"].ToString();
                string strMetroStationName = row["站点名称"].ToString();
                string strDistanceToStation = row["项目与站点间的距离"].ToString();
                string strBuildBusiness = row["开发商/投资商"].ToString();
                string strBusinessDesignCompany = row["商业设计公司"].ToString();
                string strManagementCompany = row["运营管理公司"].ToString();
                string strPropertyCompany = row["物业管理公司"].ToString();
                string strPropertyPrice = row["物业费（元/㎡·月）"].ToString();
                string strParkingOverCount = row["地上停车位数量（个）"].ToString();
                if (strParkingOverCount.Trim() == "" ||
                    strParkingOverCount == "-" || strParkingOverCount == "无")
                {
                    strParkingOverCount = "0";
                }  
                string strParkingUnderCount = row["地下停车位数量（个）"].ToString();
                if (strParkingUnderCount.Trim() == "" ||
                 strParkingUnderCount == "-" || strParkingUnderCount == "无")
                {
                    strParkingUnderCount = "0";
                }  
                string strParkingOverPrice = row["地上停车费（元/小时）"].ToString();
                if (strParkingOverPrice.Trim() == "" ||
                    strParkingOverPrice == "-" || strParkingOverPrice == "免费")
                {
                    strParkingOverPrice = "0";
                }
                string strParkingUnderPrice = row["地下停车费（元/小时）"].ToString();
                if (strParkingUnderPrice.Trim() == "" ||
                   strParkingUnderPrice == "-" || strParkingUnderPrice == "免费")
                {
                    strParkingUnderPrice = "0";
                }
                string strWeekDayMorningPerson = row["工作日上午人流量（人/30分钟）"].ToString();
                if (strWeekDayMorningPerson.Trim() == "" ||
                 strWeekDayMorningPerson == "-")
                {
                    strWeekDayMorningPerson = "0";
                }  
                string strWeekDayAfternoonPerson = row["工作日下午人流量（人/30分钟）"].ToString();
                if (strWeekDayAfternoonPerson.Trim() == "" ||
            strWeekDayAfternoonPerson == "-")
                {
                    strWeekDayAfternoonPerson = "0";
                }  
                string strWeekDayMonitorTime = row["工作日监测时间"].ToString();
                string strHolidayMorningPerson = row["节假日上午人流量（人/30分钟）"].ToString();
                if (strHolidayMorningPerson.Trim() == "" ||
            strHolidayMorningPerson == "-")
                {
                    strHolidayMorningPerson = "0";
                }  
                string strHolidayAfternoonPerson = row["节假日下午人流量（人/30分钟）"].ToString();
                if (strHolidayAfternoonPerson.Trim() == "" ||
            strHolidayAfternoonPerson == "-")
                {
                    strHolidayAfternoonPerson = "0";
                }  
                string strHolidayMonitorTime = row["节假日日监测时间"].ToString();
                 
                if (i % insertRowsCount == 0)
                {
                    strSql = new StringBuilder();
                    strSql.Append("Insert into tbbasicinfo (");
                    strSql.Append("ProjectName,");
                    strSql.Append("Address,");
                    strSql.Append("OpenTime,");
                    strSql.Append("BusinessType,");
                    strSql.Append("SellState,");
                    strSql.Append("AdministratiVeregion,");
                    strSql.Append("BigCompanyAroud,");
                    strSql.Append("SupportingAroud,");
                    strSql.Append("BusinessCircles,");
                    strSql.Append("CirclesLevel,");
                    strSql.Append("BusRouteCount,");
                    strSql.Append("BusRouteName,");
                    strSql.Append("BusStationName,");
                    strSql.Append("MetroRouteCount,");
                    strSql.Append("MetroRouteName,");
                    strSql.Append("MetroStationName,");
                    strSql.Append("DistanceToStation,");
                    strSql.Append("BuildBusiness,");
                    strSql.Append("BusinessDesignCompany,");
                    strSql.Append("ManagementCompany,");
                    strSql.Append("PropertyCompany,");
                    strSql.Append("PropertyPrice,");
                    strSql.Append("ParkingOverCount,");
                    strSql.Append("ParkingUnderCount,");
                    strSql.Append("ParkingOverPrice,");
                    strSql.Append("ParkingUnderPrice,");
                    strSql.Append("WeekDayMorningPerson,");
                    strSql.Append("WeekDayAfternoonPerson,");
                    strSql.Append("WeekDayMonitorTime,");
                    strSql.Append("HolidayMorningPerson,");
                    strSql.Append("HolidayAfternoonPerson,");
                    strSql.Append("HolidayMonitorTime)");                    
                    strSql.Append(" value  ");
                }
                strSql.Append("(");
                strSql.Append("'");
                strSql.Append(strProName);
                strSql.Append("','");                 
                strSql.Append(strAddress);
                strSql.Append("','");                
                strSql.Append(strOpenTime);
                strSql.Append("','");                 
                strSql.Append(strBusinessType);
                strSql.Append("','");                 
                strSql.Append(strSellState);
                strSql.Append("','");                 
                strSql.Append(strXzq);
                strSql.Append("','");                 
                strSql.Append(strBigCompanyAroud);
                strSql.Append("','");                
                strSql.Append(strSupportingAroud);
                strSql.Append("','");                 
                strSql.Append(strBusinessCircles);
                strSql.Append("','");                
                strSql.Append(strCirclesLevel);
                strSql.Append("',");                 
                strSql.Append(strBusRouteCount);
                strSql.Append(",'");                
                strSql.Append(strBusRouteName);
                strSql.Append("','");                 
                strSql.Append(strBusStationName);
                strSql.Append("',");                 
                strSql.Append(strMetroRouteCount);
                strSql.Append(",'");                 
                strSql.Append(strMetroRouteName);
                strSql.Append("','");
                strSql.Append(strMetroStationName);
                strSql.Append("','");                 
                strSql.Append(strDistanceToStation);
                strSql.Append("','");                 
                strSql.Append(strBuildBusiness);
                strSql.Append("','");                 
                strSql.Append(strBusinessDesignCompany);
                strSql.Append("','");                 
                strSql.Append(strManagementCompany);
                strSql.Append("','");                 
                strSql.Append(strPropertyCompany);
                strSql.Append("','");                 
                strSql.Append(strPropertyPrice);
                strSql.Append("',");                 
                strSql.Append(strParkingOverCount);
                strSql.Append(",");                 
                strSql.Append(strParkingUnderCount);
                strSql.Append(",");                 
                strSql.Append(strParkingOverPrice);
                strSql.Append(",");        
                strSql.Append(strParkingUnderPrice);
                strSql.Append(",");          
                strSql.Append(strWeekDayMorningPerson);
                strSql.Append(",");             
                strSql.Append(strWeekDayAfternoonPerson);
                strSql.Append(",'");      
                strSql.Append(strWeekDayMonitorTime);
                strSql.Append("',");          
                strSql.Append(strHolidayMorningPerson);
                strSql.Append(",");
                strSql.Append(strHolidayAfternoonPerson);
                strSql.Append(",'");
                strSql.Append(strHolidayMonitorTime);                 
                strSql.Append("'),");

                if ((i + 1) % insertRowsCount == 0
                    || i == dt.Rows.Count - 1)
                {
                    strSql.Remove(strSql.Length - 1, 1);
                    try
                    {
                        succeedrows += DbHelper.ExecuteSql(strSql.ToString());
                    }
                    catch (Exception ex)
                    {
                        XtraMessageBox.Show(ex.Message);
                    }

                }
            }

            if (dt.Rows.Count == succeedrows)
            {
                XtraMessageBox.Show("添加成功！");
                BeginInvoke(new MethodInvoker(() =>
                {
                    btn_excelPath.Text = "";
                    gridControl2.DataSource = null;
                    gridView2.GroupPanelText = "请选择想要导入的Excel表！";
                }));
            }
            else
            {
                int failedCount = dt.Rows.Count - succeedrows;
                XtraMessageBox.Show("共记" + dt.Rows.Count + "条数据，其中"
                    + failedCount + "条数据添加失败！");
            }
            BeginInvoke(new MethodInvoker(() =>
            {
               
                btn_excelPath.Enabled = true;
                btn_add.Enabled = true;
            }));
        }

        private void btn_add_Click(object sender, EventArgs e)
        {
            if (btn_excelPath.Text.Trim()=="")
            {
                XtraMessageBox.Show("请先选择Excel！");
                return;
            }
            if (!CheckDataValid())
            {
                return;
            }
             
            btn_excelPath.Enabled = false;
            btn_add.Enabled = false;
            Thread t = new Thread(AddData) { IsBackground = true };
            t.Start();
        }

        #region 数据完整性检测
        private bool CheckDataValid()
        {
            DataTable dt = (DataTable)gridControl2.DataSource;
            StringBuilder strErrors = new StringBuilder();
           
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                string strProName = dr["项目名称"].ToString();
                string strPubTime = dr["开业时间"].ToString();
                string strXzq = dr["所属行政区"].ToString();
                string strBusRouteCount = dr["公交线路数量"].ToString();
                string strMetroRouteCount = dr["地铁线路数量"].ToString();
                string strParkingOverCount = dr["地上停车位数量（个）"].ToString();
                string strParkingUnderCount = dr["地下停车位数量（个）"].ToString();
                string strWeekDayMorningPerson = dr["工作日上午人流量（人/30分钟）"].ToString();
                string strWeekDayAfternoonPerson = dr["工作日下午人流量（人/30分钟）"].ToString();
                string strHolidayMorningPerson = dr["节假日上午人流量（人/30分钟）"].ToString();
                string strHolidayAfternoonPerson = dr["节假日下午人流量（人/30分钟）"].ToString();
                string strParkingOverPrice = dr["地上停车费（元/小时）"].ToString();
                string strParkingUnderPrice = dr["地下停车费（元/小时）"].ToString();

                if (strProName.Trim() == "")
                {
                    strErrors.Append("第"+(i+2)+"行数据中的【项目名称】为空！\r\n"); 
                }
                DateTime pubTime = new DateTime();
                if (!DateTime.TryParse(strPubTime, out pubTime))
                {
                    strErrors.Append("第" + (i + 2)
                   + "行数据中【开业时间】的数据格式有误，不是正确的日期格式！\r\n");
                }
                if (strXzq.Trim()=="")
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【行政区】未填！\r\n");
                }
                int intTemp;
                if (strBusRouteCount.Trim()!="" && 
                    strBusRouteCount.Trim()!="无" &&
                    strBusRouteCount.Trim() != "-" &&
                    !int.TryParse(strBusRouteCount, out intTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【公交线路数量】不为正确的整数！\r\n");
                }
                if (strMetroRouteCount.Trim()!="" && 
                    strMetroRouteCount.Trim()!="无" &&
                    strMetroRouteCount.Trim() != "-" &&
                    !int.TryParse(strMetroRouteCount, out intTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【地铁线路数量】未填！\r\n");
                }
                if ( strParkingOverCount.Trim()!="" &&
                     strParkingOverCount !="无"&&
                     strParkingOverCount !="-" &&
                    !int.TryParse(strParkingOverCount, out intTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【地上停车位数量（个）】未填！\r\n");
                }
                if (strParkingUnderCount.Trim()!="" &&
                    strParkingUnderCount!="无" &&
                    strParkingUnderCount != "-" &&
                    !int.TryParse(strParkingUnderCount, out intTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【地下停车位数量（个）】未填！\r\n");
                }
                if (strParkingUnderCount.Trim()!="" &&
                    strParkingUnderCount!="无" &&
                    strParkingUnderCount != "-" &&
                    !int.TryParse(strParkingUnderCount, out intTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【地下停车位数量（个）】未填！\r\n");
                }
                float floatTemp;
                if (strParkingOverPrice.Trim() != "" &&
                    strParkingOverPrice != "免费" &&
                    strParkingOverPrice != "-"
                    && !float.TryParse(strParkingOverPrice, out floatTemp)
                    )
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【地上停车费（元/小时）】未填！\r\n");
                }
                if (strParkingUnderPrice.Trim() != "" &&
                    strParkingUnderPrice != "免费" &&
                    strParkingUnderPrice != "-"
                    && !float.TryParse(strParkingUnderPrice, out floatTemp)
                    )
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【地下停车费（元/小时）】未填！\r\n");
                }


                if (strWeekDayMorningPerson.Trim()!="" &&
                    strWeekDayMorningPerson != "-" &&
                    !int.TryParse(strWeekDayMorningPerson, out intTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【工作日上午人流量（人/30分钟）】未填！\r\n");
                }
                if (strWeekDayAfternoonPerson.Trim()!="" && 
                    strWeekDayAfternoonPerson !="-" &&
                    !int.TryParse(strWeekDayAfternoonPerson, out intTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【工作日下午人流量（人/30分钟）】未填！\r\n");
                }
                if (strHolidayMorningPerson.Trim()!=""  &&
                    strHolidayMorningPerson != "-" && 
                    !int.TryParse(strHolidayMorningPerson, out intTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【节假日上午人流量（人/30分钟）】未填！\r\n");
                }
                if (strHolidayAfternoonPerson.Trim()!="" &&
                    strHolidayAfternoonPerson != "-" && 
                    !int.TryParse(strHolidayAfternoonPerson, out intTemp))
                {
                    strErrors.Append("第" + (i + 2) + "行数据中【节假日下午人流量（人/30分钟）】未填！\r\n");
                }
            }
            if (strErrors.ToString() != "")
            {                
                XtraMessageBox.Show("您选择的Excel中的数据存在以下错误：\r\n"+
                    strErrors);
                return false;
            }              
            return true;
        }
         
        private bool CheckExcelColumnsName(DataTable dt)
        {
            StringBuilder strErrors = new StringBuilder();
            if (!dt.Columns.Contains("项目名称"))
            {
                strErrors.Append("【项目名称】、");
            }
            if (!dt.Columns.Contains("项目地址"))
            {
                strErrors.Append("【项目地址】、");
            }
            if (!dt.Columns.Contains("开业时间"))
            {
                strErrors.Append("【开业时间】、");
            }
            if (!dt.Columns.Contains("商业业态类型"))
            {
                strErrors.Append("【商业业态类型】、");
            }
            if (!dt.Columns.Contains("销售状态"))
            {
                strErrors.Append("【销售状态】、");
            }
            if (!dt.Columns.Contains("所属行政区"))
            {
                strErrors.Append("【所属行政区】、");
            }
            if (!dt.Columns.Contains("周边大型企业"))
            {
                strErrors.Append("【周边大型企业】、");
            }
            if (!dt.Columns.Contains("周边配套"))
            {
                strErrors.Append("【周边配套】、");
            }
            if (!dt.Columns.Contains("所属商圈"))
            {
                strErrors.Append("【所属商圈】、");
            }
            if (!dt.Columns.Contains("商圈评级"))
            {
                strErrors.Append("【商圈评级】、");
            }
            if (!dt.Columns.Contains("公交线路数量"))
            {
                strErrors.Append("【公交线路数量】");
            }
            if (!dt.Columns.Contains("公交线路名称"))
            {
                strErrors.Append("【公交线路名称】");
            }
            if (!dt.Columns.Contains("公交站点名称"))
            {
                strErrors.Append("【公交站点名称】");
            }
            if (!dt.Columns.Contains("地铁线路数量"))
            {
                strErrors.Append("【地铁线路数量】");
            }
            if (!dt.Columns.Contains("地铁线路"))
            {
                strErrors.Append("【地铁线路】");
            }
            if (!dt.Columns.Contains("站点名称"))
            {
                strErrors.Append("【站点名称】");
            }
            if (!dt.Columns.Contains("项目与站点间的距离"))
            {
                strErrors.Append("【项目与站点间的距离】");
            }
            if (!dt.Columns.Contains("开发商/投资商"))
            {
                strErrors.Append("【开发商/投资商】");
            }
            if (!dt.Columns.Contains("商业设计公司"))
            {
                strErrors.Append("【商业设计公司】");
            }
            if (!dt.Columns.Contains("运营管理公司"))
            {
                strErrors.Append("【运营管理公司】");
            }
            if (!dt.Columns.Contains("物业管理公司"))
            {
                strErrors.Append("【物业管理公司】");
            }
            if (!dt.Columns.Contains("物业费（元/㎡·月）"))
            {
                strErrors.Append("【物业费（元/㎡·月）】");
            }
            if (!dt.Columns.Contains("地上停车位数量（个）"))
            {
                strErrors.Append("【地上停车位数量（个）】");
            }
            if (!dt.Columns.Contains("地下停车位数量（个）"))
            {
                strErrors.Append("【地下停车位数量（个）】");
            }
            if (!dt.Columns.Contains("地上停车费（元/小时）"))
            {
                strErrors.Append("【地上停车费（元/小时）】");
            }
            if (!dt.Columns.Contains("地下停车费（元/小时）"))
            {
                strErrors.Append("【地下停车费（元/小时）】");
            }
            if (!dt.Columns.Contains("工作日上午人流量（人/30分钟）"))
            {
                strErrors.Append("【工作日上午人流量（人/30分钟）】");
            }
            if (!dt.Columns.Contains("工作日下午人流量（人/30分钟）"))
            {
                strErrors.Append("【工作日下午人流量（人/30分钟）】");
            }
            if (!dt.Columns.Contains("工作日监测时间"))
            {
                strErrors.Append("【工作日监测时间】");
            }
            if (!dt.Columns.Contains("节假日上午人流量（人/30分钟）"))
            {
                strErrors.Append("【节假日上午人流量（人/30分钟）】");
            }
            if (!dt.Columns.Contains("节假日下午人流量（人/30分钟）"))
            {
                strErrors.Append("【节假日下午人流量（人/30分钟）】");
            }
            if (!dt.Columns.Contains("节假日日监测时间"))
            {
                strErrors.Append("【节假日日监测时间】");
            }
            if (strErrors.ToString() != "")
            {
                XtraMessageBox.Show("您选择的Excel表数据不正常,缺少以下数据列:\r\n" +
                                    strErrors);
                return false;
            }
            return true;
        }
        #endregion
    }
}